import com.mongodb.BasicDBObject;
import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.model.UpdateOptions;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bson.Document;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;


public class 存货账面余额 {
    public static void main(String[] args) throws IOException {
        MongoCollection<Document> target = new MongoClient("192.168.1.175", 27017)
                .getDatabase("NFSD").getCollection("存货账面余额");
        InputStream input = new FileInputStream( "C:\\Users\\Administrator\\Desktop\\转换任务\\NFSD待转换(1)\\存货\\存货THS账面余额2018-2019中报+年报.xlsx" );
        XSSFWorkbook workbook = new XSSFWorkbook(input);
        XSSFSheet sheet = workbook.getSheetAt(0);
//        XSSFWorkbook workbook1 = new XSSFWorkbook();
//        XSSFSheet sheet1 = workbook1.createSheet();
        int rowNum = sheet.getLastRowNum();

        for (int i = 1; i <=rowNum; i++) {
            XSSFRow row = sheet.getRow(i);
//            String Code = row.getCell(0).getStringCellValue();

            if ( row != null){
                int coloumNum=row.getPhysicalNumberOfCells();
                if(coloumNum >2){
//                    System.out.println(coloumNum);
                    String code = row.getCell(0).getStringCellValue();
//                System.out.println(row.getCell(0).getStringCellValue());
                    String name = row.getCell(1).getStringCellValue();
//                    System.out.println(code);
//                    System.out.println(name);

                    for (int j = 2; j <=coloumNum; j++) {

                        if(row.getCell(j) != null){
                            String title = sheet.getRow(0).getCell(j).getStringCellValue();
//                            System.out.println(title);
                            String[] s_all = title.split("\n");
                            if(s_all.length == 4){
                                String timeR = s_all[1].replace("[报告期]","").trim();
                                String xiangmu = s_all[0].replace("存货明细－","").trim();
                                System.out.println(xiangmu);
                                String Id = code+"_"+ name+"_"+timeR+"_"+xiangmu;
//                                String Id = code+"_"+ name+"_"+timeR;
//                                System.out.println(s_all.length);
//                                System.out.println(title);
                                Document document = new Document("_id",Id);
//                                System.out.println(Id);
//                                System.out.println(title);
                                document.append("code",code);
                                document.append("name",name);
                                document.append("报告期",timeR);
//                                String xiangmu = s_all[0].replace("存货明细－","").trim();
                                document.append("存货项目",xiangmu);
//                                System.out.println(xiangmu);
                                String key =  s_all[2].replace("[数据类别]","").trim();
                                Object value = row.getCell(j);
//                                System.out.println(value.toString());
                                document.append(key,value.toString());
                                target.updateOne(new BasicDBObject("_id", document.getString("_id")), new BasicDBObject("$set", document), (new UpdateOptions()).upsert(true));
                                System.out.println(document);
                            }

                        }

                    }
                }

            }

        }

    }
}
